/**
 * 
 */
package vehicleService;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import vehicleEntity.RentalVehicleEntity;
import vehicleEntity.SaleVehicleEntity;
import constants.Constants;


/**
 * @author Yokesh Kumar
 *
 */
public class MoveService {

	private Connection connection;
	private PreparedStatement statement;
	
	public MoveService(){
		try {
			Class.forName(Constants.JDBC_DRIVER);
			connection = DriverManager.getConnection(Constants.DB_URL,Constants.USER_NAME,Constants.PWD);
		} 
		catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}
	
	/**
	 * This method returns the result set values of vehicles
	 * @param oldTime
	 * @Pre The oldTime values are retrieved from the MoveLogic class
	 * @Post Values selected from database are passed back to the MoveLogic class
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet searchOldRentVehicles(int oldTime) throws SQLException{
		ResultSet rs = null;
		String query = "select p.vehicleId, p.purchasedDate, p.manufDate ,p.amount,v.RegNo,v.vehicleType,v.vehicleName from purchased_vehicle p, vehicle v, rental_vehicle r where  p.manufDate + Interval ? year < current_date and p.vehicleId = v.vehicleId and p.vehicleId = r.vehicleId and r.status='available'";
		try {
			statement = connection.prepareStatement(query);
			statement.setInt(1, oldTime);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}
	
	/**
	 * This method returns the result set of purchase table values from the database
	 * @param none
	 * @Pre Purchase Vehicle entity values are set in the vehicleEntity class
	 * @Post The selected values are passed to the MoveLogic class
	 * @return none
	 * @throws SQLException 
	 */
	public ResultSet fetchPurchasedvehicles() throws SQLException {
		ResultSet rs = null;
		String query = "select p.vehicleId, p.purchaseddate, p.manufdate ,p.amount,v.RegNo,v.vehicleType,v.vehicleName from purchased_vehicle p, vehicle v where p.vehicleId = v.vehicleId and v.vehicleId not in (select vehicleId from rental_vehicle) and v.vehicleId not in (select saleVehicleId from sale_vehicle)";
		try {
			statement = connection.prepareStatement(query);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}
	
	/**
	 * This method returns the result set of sale table values from the database
	 * @Param none
	 * @Pre Sale Vehicle entity values are set in the vehicleEntity class
	 * @Post The selected values are then passed to the MoveLogic class
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet fetchSaleVehicles() throws SQLException {
		ResultSet rs = null;
		String query = "select p.vehicleId, p.purchaseddate, p.manufdate ,p.amount,v.RegNo,v.vehicleType,v.vehicleName from purchased_vehicle p, vehicle v, sale_vehicle s where s.soldAmount is null and s.saleVehicleId = v.vehicleId and v.vehicleId = p.vehicleId";
		try {
			statement = connection.prepareStatement(query);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}
	/**
	 * this method searches for the vehicle in the PurchasedVehicle table
	 * @param vehicleId
	 * @Pre vehicleId values are retrieved from the MoveLogic class
	 * @Post the selected values are then passed back to the MoveLogic class
	 * @return none
	 * @throws SQLException 
	 */
	public ResultSet searchPurchase(int vehicleId) throws SQLException{
		ResultSet rs = null;
		String query = "select p.vehicleId, p.purchaseddate, p.manufdate ,p.amount,v.RegNo,v.vehicleType,v.vehicleName from purchased_vehicle p, vehicle v where p.vehicleId = ? and p.vehicleId = v.vehicleId";
		try {
			statement = connection.prepareStatement(query);
			statement.setInt(1, vehicleId);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}
	
	/**
	 * This method moves the vehicle with vehicle ID from purchase status 
	 * to Rent status 
	 * @param vehicleId
	 * @Pre rentalEntity values are retrieved from the MoveLogic class
	 * @Post The rental vehicle table column values are inserted in the database
	 * @return none
	 * @throws SQLException 
	 * 
	 */
	
	public void movePurchasetoRent(RentalVehicleEntity rentalEntity) throws SQLException{
		String query = "INSERT INTO rental_vehicle values(?,?,null,null)";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setInt(1, rentalEntity.getVehicleId());
			statement.setString(2, rentalEntity.getStatus());
			statement.executeUpdate();
			connection.commit();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
	}
	
	/**
	 * this method searches for the vehicle in the SaleVehicle table
	 * @param vehicleId
	 * @Pre vehicleId values are retrieved from the MoveLogic class
	 * @Post the resultset values retrieved from the select query statement is passed back to the MoveLogic class
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet searchSale(int vehicleId) throws SQLException{
		ResultSet rs = null;
		String query = "select p.vehicleId, p.purchaseddate, p.manufdate ,p.amount,v.RegNo,v.vehicleType,v.vehicleName from purchased_vehicle p, vehicle v, sale_vehicle s where s.saleVehicleId = ? and s.saleVehicleId = v.vehicleId and s.saleVehicleId = p.vehicleId";
		try {
			statement = connection.prepareStatement(query);
			statement.setInt(1, vehicleId);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}
	
	/**
	 * This method moves the vehicle with vehicle ID from salestatus 
	 * to Rent status 
	 * @param vehicleId
	 * @Pre rent entity values are retrieved from the MoveLogic class
	 * @Post Insertion and Deletion are done in the rental_vehicle and sale_vehicle tables
	 * @return none
	 * @throws SQLException 
	 */
	
	public void moveSaleToRent(RentalVehicleEntity rentEntity) throws SQLException{
		String query = "INSERT INTO rental_vehicle values(?,?,null,null)";
		String query2 = "DELETE FROM sale_vehicle where saleVehicleId = ?";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setInt(1, rentEntity.getVehicleId());
			statement.setString(2, rentEntity.getStatus());
			statement.executeUpdate();
			
			statement = connection.prepareStatement(query2);
			statement.setInt(1, rentEntity.getVehicleId());
			statement.executeUpdate();
			connection.commit();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
	}
	
	/**
	 * this method searches for the vehicle in the RentVehicle table
	 * @param vehicleId
	 * @Pre vehicleId values are retrieved from the MoveLogic class
	 * @Post the resultset values retrieved from the select statement is passed back to the MoveLogic class
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet searchRent(int vehicleId) throws SQLException{
		ResultSet rs = null;
		String query = "select p.vehicleId, p.purchaseddate, p.manufdate ,p.amount,v.RegNo,v.vehicleType,v.vehicleName from purchased_vehicle p, vehicle v, rental_vehicle r where r.vehicleId = ? and r.vehicleId = v.vehicleId and r.vehicleId = p.vehicleId and r.status='available'";
		try {
			statement = connection.prepareStatement(query);
			statement.setInt(1, vehicleId);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}
	
	/**
	 * This method moves the vehicle with vehicle ID from Rent status 
	 * to Sale status 
	 * @param saleEntity
	 * @Pre saleEntity values are retrieved from the MoveLogic class
	 * @Post Insertion and deletion of values are done in the sale_vehicle and rental_vehicle table based on the saleEntity values
	 * @return none
	 * @throws SQLException 
	 */
	
	public void moveRentToSale(SaleVehicleEntity saleEntity) throws SQLException{
		String query = "INSERT INTO sale_vehicle values(?,current_date,?,null,null,null)";
		String query2 = "DELETE FROM rental_vehicle where vehicleId = ?";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setInt(1, saleEntity.getVehicleId());
			statement.setDouble(2, saleEntity.getSaleAmount());
			statement.executeUpdate();
			
			statement = connection.prepareStatement(query2);
			statement.setInt(1, saleEntity.getVehicleId());
			statement.executeUpdate();
			
			connection.commit();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
	}

	public void closeConnection(){
		try{
			if(statement != null){
				statement.close();
			}
			if(connection != null){
				connection.close();
			}
		}
		catch(SQLException sqlExec){
			sqlExec.printStackTrace();
		}
	}

	

	
	
	
}
